DROP TABLE LOG_ACTIONS CASCADE CONSTRAINTS ;

CREATE TABLE LOG_ACTIONS
  (
    ID            	 NUMBER NOT NULL ,
	DATE_OF_RECORD	 DATE NOT NULL ,
    TOKEN 			 VARCHAR2 (256) NOT NULL ,
    URL				 VARCHAR2 (256) NOT NULL ,
    TIME_			 NUMBER NOT NULL 
  ) ;
ALTER TABLE LOG_ACTIONS ADD CONSTRAINT LOG_ACTIONS_PK PRIMARY KEY
(
  ID
)
;
ALTER TABLE LOG_ACTIONS ADD CONSTRAINT LOG_ACTIONS__UN UNIQUE
(
  DATE_OF_RECORD , TOKEN , URL
)
;

CREATE SEQUENCE LOG_ACTIONS_ID_SEQ START WITH 1 NOCACHE ORDER ;
CREATE OR REPLACE TRIGGER LOG_ACTIONS_ID_TRG BEFORE
  INSERT ON LOG_ACTIONS FOR EACH ROW BEGIN
  SELECT LOG_ACTIONS_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

/*TRIGGERS*/

/*COMMENTS*/

CREATE OR REPLACE PACKAGE Timers IS

  FUNCTION addRecord(url_ VARCHAR2, token_ VARCHAR2, time_ NUMBER) RETURN VARCHAR2;
  Function getStats(token_ VARCHAR2) RETURN CLOB;
  function getTime(token_ varchar2, url_ varchar2) return varchar2;
	
END Timers;
/

CREATE OR REPLACE PACKAGE BODY Timers IS

FUNCTION addRecord(url_ VARCHAR2, token_ VARCHAR2, time_ NUMBER) RETURN VARCHAR2

IS
  ret JSON := json();

BEGIN

  INSERT INTO LOG_ACTIONS (DATE_OF_RECORD, TOKEN, URL, TIME_) VALUES (SYSDATE, token_, url_, time_);
  COMMIT;
  ret.put('inf', 'Record added successfully');
  RETURN ret.to_char;
  
EXCEPTION 
  WHEN OTHERS THEN
    ret.put('err', sqlerrm);
    RETURN ret.to_char;

END addrecord;

Function getStats(token_ VARCHAR2) RETURN CLOB

IS
  cursor cur(token__ varchar2) is 
  select URL, sum(time_) as sum_time from log_actions where token = token__ and rownum < 100 group by url order by sum_time desc;
  obj json;
  res json_list := json_list();
  c_res clob;

BEGIN
  for rec in cur(token_) loop
    obj := json();
    obj.put('url', rec.url);
    obj.put('time', rec.sum_time);
    res.append(obj.to_json_value());
  end loop;
  
  c_res := empty_clob();
  dbms_lob.createtemporary(c_res, true);
  
  res.to_clob(c_res);
  return c_res;
END getStats;

function getTime(token_ varchar2, url_ varchar2) return varchar2

is
  ret JSON := json();
  s_time number;
  
begin  
  Select sum(time_) into s_time from LOG_ACTIONS where TOKEN = token_ and URL = url_;
  if s_time is null
  then
    s_time := 0;
  end if;
  ret.put('inf', 'Good');
  ret.put('time', s_time);
  RETURN ret.to_char;
  
EXCEPTION 
  WHEN OTHERS THEN
    ret.put('err', sqlerrm);
    RETURN ret.to_char;
    
end getTime;    

end Timers;
/
